using System.Collections.Concurrent;
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using Npgsql;
using MySql.Data.MySqlClient;
using SPI.AgentServer.Configuration;
using SPI.AgentServer.Models;

namespace SPI.AgentServer.Services.Metadata;

public class SchemaRegistry : ISchemaRegistry
{
    private readonly DatabaseConfiguration _config;
    private readonly ILogger<SchemaRegistry> _logger;
    private readonly ConcurrentDictionary<string, DatabaseSchema> _schemaCache = new();

    public SchemaRegistry(IOptions<DatabaseConfiguration> options, ILogger<SchemaRegistry> logger)
    {
        _config = options.Value;
        _logger = logger;
    }

    public async Task<List<string>> ListDatabasesAsync(CancellationToken cancellationToken = default)
    {
        return await Task.FromResult(_config.Connections.Select(c => c.Name).ToList());
    }

    public async Task<List<string>> ListTablesAsync(string? databaseName = null, CancellationToken cancellationToken = default)
    {
        var connection = GetConnectionInfo(databaseName);
        var tables = new List<string>();

        using var conn = CreateConnection(connection);
        conn.Open();

        var query = connection.Provider.ToLower() switch
        {
            "sqlserver" => "SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'",
            "postgresql" => "SELECT schemaname || '.' || tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema')",
            "mysql" => "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()",
            _ => throw new NotSupportedException($"Provider {connection.Provider} not supported")
        };

        using var cmd = conn.CreateCommand();
        cmd.CommandText = query;
        using var reader = cmd.ExecuteReader();
        
        while (reader.Read())
        {
            tables.Add(reader.GetString(0));
        }

        return tables;
    }

    public async Task<TableSchema?> GetTableSchemaAsync(string tableName, string? databaseName = null, CancellationToken cancellationToken = default)
    {
        var connection = GetConnectionInfo(databaseName);
        var cacheKey = $"{connection.Name}:{tableName}";

        // Check cache first
        if (_schemaCache.TryGetValue(cacheKey, out var cachedSchema))
        {
            var table = cachedSchema.Tables.FirstOrDefault(t => 
                string.Equals(t.TableName, tableName, StringComparison.OrdinalIgnoreCase) ||
                string.Equals($"{t.SchemaName}.{t.TableName}", tableName, StringComparison.OrdinalIgnoreCase));
            
            if (table != null)
                return table;
        }

        // Load from database
        using var conn = CreateConnection(connection);
        conn.Open();

        var schema = new TableSchema
        {
            DatabaseName = connection.Name,
            TableName = tableName
        };

        // Parse schema and table name
        var parts = tableName.Split('.');
        if (parts.Length == 2)
        {
            schema.SchemaName = parts[0];
            schema.TableName = parts[1];
        }

        // Get columns
        var columnsQuery = connection.Provider.ToLower() switch
        {
            "sqlserver" => @"
                SELECT 
                    c.COLUMN_NAME,
                    c.DATA_TYPE,
                    c.CHARACTER_MAXIMUM_LENGTH,
                    c.IS_NULLABLE,
                    c.COLUMN_DEFAULT,
                    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END as IsPrimaryKey,
                    COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') as IsIdentity
                FROM INFORMATION_SCHEMA.COLUMNS c
                LEFT JOIN (
                    SELECT ku.TABLE_SCHEMA, ku.TABLE_NAME, ku.COLUMN_NAME
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
                        ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
                    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
                ) pk ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA AND c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAME
                WHERE c.TABLE_NAME = @tableName AND (@schemaName IS NULL OR c.TABLE_SCHEMA = @schemaName)",
            "postgresql" => @"
                SELECT 
                    c.column_name,
                    c.data_type,
                    c.character_maximum_length,
                    c.is_nullable,
                    c.column_default,
                    CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END as is_primary_key,
                    c.is_identity = 'YES' as is_identity
                FROM information_schema.columns c
                LEFT JOIN (
                    SELECT ku.column_name
                    FROM information_schema.table_constraints tc
                    JOIN information_schema.key_column_usage ku USING (constraint_name)
                    WHERE tc.constraint_type = 'PRIMARY KEY' 
                    AND tc.table_name = @tableName
                ) pk ON c.column_name = pk.column_name
                WHERE c.table_name = @tableName AND (@schemaName IS NULL OR c.table_schema = @schemaName)",
            _ => throw new NotSupportedException($"Provider {connection.Provider} not supported")
        };

        using var cmd = conn.CreateCommand();
        cmd.CommandText = columnsQuery;
        cmd.Parameters.Add(CreateParameter(cmd, "@tableName", schema.TableName));
        cmd.Parameters.Add(CreateParameter(cmd, "@schemaName", parts.Length == 2 ? schema.SchemaName : null));

        using var reader = cmd.ExecuteReader();
        
        while (reader.Read())
        {
            var column = new ColumnSchema
            {
                ColumnName = reader.GetString(0),
                DataType = reader.GetString(1),
                MaxLength = reader.IsDBNull(2) ? null : reader.GetInt32(2),
                IsNullable = reader.GetString(3) == "YES",
                DefaultValue = reader.IsDBNull(4) ? null : reader.GetString(4),
                IsPrimaryKey = reader.GetBoolean(5),
                IsIdentity = reader.GetBoolean(6)
            };

            schema.Columns.Add(column);
            
            if (column.IsPrimaryKey)
                schema.PrimaryKeys.Add(column.ColumnName);
        }

        return schema;
    }

    public async Task<List<string>> GetSampleSqlAsync(string tableName, string? databaseName = null, CancellationToken cancellationToken = default)
    {
        var schema = await GetTableSchemaAsync(tableName, databaseName, cancellationToken);
        if (schema == null)
            return new List<string>();

        var samples = new List<string>
        {
            $"-- Select all columns (limited)\nSELECT TOP 100 * FROM {tableName}",
            $"-- Count total records\nSELECT COUNT(*) as total_count FROM {tableName}",
            $"-- Select specific columns\nSELECT {string.Join(", ", schema.Columns.Take(3).Select(c => c.ColumnName))} FROM {tableName}"
        };

        if (schema.Columns.Any(c => c.DataType.Contains("date", StringComparison.OrdinalIgnoreCase)))
        {
            var dateColumn = schema.Columns.First(c => c.DataType.Contains("date", StringComparison.OrdinalIgnoreCase));
            samples.Add($"-- Filter by date\nSELECT * FROM {tableName} WHERE {dateColumn.ColumnName} >= DATEADD(day, -7, GETDATE())");
        }

        return samples;
    }

    public async Task RefreshSchemaAsync(string? databaseName = null, CancellationToken cancellationToken = default)
    {
        if (databaseName != null)
        {
            _schemaCache.TryRemove(databaseName, out _);
        }
        else
        {
            _schemaCache.Clear();
        }
        
        await Task.CompletedTask;
    }

    public bool HasPermission(string tableName, string operation, string? databaseName = null)
    {
        // For now, allow all SELECT operations
        return operation.Equals("SELECT", StringComparison.OrdinalIgnoreCase);
    }

    private Configuration.ConnectionInfo GetConnectionInfo(string? databaseName)
    {
        if (string.IsNullOrEmpty(databaseName))
        {
            return _config.Connections.FirstOrDefault(c => c.IsDefault) ?? _config.DefaultConnection;
        }

        return _config.Connections.FirstOrDefault(c => 
            string.Equals(c.Name, databaseName, StringComparison.OrdinalIgnoreCase)) 
            ?? throw new ArgumentException($"Database '{databaseName}' not found");
    }

    private IDbConnection CreateConnection(Configuration.ConnectionInfo connectionInfo)
    {
        return connectionInfo.Provider.ToLower() switch
        {
            "sqlserver" => new SqlConnection(connectionInfo.ConnectionString),
            "postgresql" => new NpgsqlConnection(connectionInfo.ConnectionString),
            "mysql" => new MySqlConnection(connectionInfo.ConnectionString),
            _ => throw new NotSupportedException($"Provider {connectionInfo.Provider} not supported")
        };
    }

    private IDbDataParameter CreateParameter(IDbCommand command, string name, object? value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value ?? DBNull.Value;
        return parameter;
    }
}
